﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace University.Workshop.DataAccess
{
    public static class AnalysisReportData
    {
        const string ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Pharmacy;Integrated Security=True";

        public static DataTable GeneralQuery(DateTime dateStar, DateTime dateEnd)
        {
            string sqlSentence = "SELECT distinct S.CodProduct AS Codigo_Producto, P.Name AS Nombre, PRE.Description, Sum (S.Quantity) AS total FROM SaleDetail S, Product P, Sale SA , Presentation PRE WHERE S.CodeSale = SA.Code and S.CodProduct = P.Code and P.PresentationCode = PRE.Code and (SA.DateSale Between @dateStar And @dateEnd) GROUP BY S.CodProduct, P.Name , PRE.Description"; 
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;

            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@dateStar", dateStar);
                cmd.Parameters.AddWithValue("@dateEnd", dateEnd);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table;
            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }



        public static DataTable GeneralQueryType(DateTime dateStar, DateTime dateEnd, string typeProduct)
        {
            string sqlSentence;
            /*if (typeProduct == "General") 
            {
                sqlSentence = "SELECT distinct S.CodProduct AS Codigo_Producto, P.Name AS Nombre, PRE.Description, Sum (S.Quantity) AS total FROM SaleDetail S, Product P, Sale SA , Presentation PRE WHERE S.CodeSale = SA.Code and S.CodProduct = P.Code and P.PresentationCode = PRE.Code and (SA.DateSale Between @dateStar And @dateEnd) GROUP BY S.CodProduct, P.Name , PRE.Description";
            }/*/
            
                sqlSentence = "SELECT distinct S.CodProduct AS Codigo_Producto, P.Name AS Nombre, PRE.Description, Sum (S.Quantity) AS total FROM SaleDetail S, Product P, Sale SA , Presentation PRE WHERE S.CodeSale = SA.Code and S.CodProduct = P.Code and P.PresentationCode = PRE.Code and PRE.Description = @type and (SA.DateSale Between @dateStar And @dateEnd) GROUP BY S.CodProduct, P.Name , PRE.Description";
            
                         SqlConnection cnn = null;
            SqlConnection tempCnn = null;

            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@type", typeProduct);
                cmd.Parameters.AddWithValue("@dateStar", dateStar);
                cmd.Parameters.AddWithValue("@dateEnd", dateEnd);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table;
            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }
    }
}
